package com.mangguo.tingjian.content.mapper;

import com.mangguo.tingjian.content.controller.rsp.ContentSimpleRsp;
import com.mangguo.tingjian.content.dto.ContentDataAudioTableResult;
import com.mangguo.tingjian.content.dto.ContentDataAuthorTableResult;
import com.mangguo.tingjian.content.dto.TagCountResult;
import com.mangguo.tingjian.content.entity.DwPlayer;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mangguo.tingjian.user.dto.CountResult;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * play同步表 Mapper 接口
 * </p>
 *
 * @author auto
 * @since 2019-11-24
 */
@Mapper
public interface DwPlayerMapper extends BaseMapper<DwPlayer> {
    @Select("select count(1) from dw_player where mdate >= #{start} and mdate <= #{end}")
    Long countPlayBetweenDate(@Param("start")Integer start,
                              @Param("end") Integer end);

    @Select("select sum(lestenSec) from dw_player where mdate >= #{start} and mdate <= #{end}")
    Long sumPlayTimeBetweenDate(@Param("start")Integer start,
                                @Param("end") Integer end);

    @Select("select count(1) from (select distinct imei from dw_player where mdate >= #{start} and mdate <= #{end}) as a")
    Long countPlayImeiBetweenDate(@Param("start")Integer start,
                                  @Param("end") Integer end);

    @Select("<script>" +
            "select count(1) AS count,contentName,albumName from dw_player " +
            "where mdate &gt;= #{start} and mdate &lt;= #{end} " +
            "and " +
            "<if test='lvl1 != null and lvl1 != -1L'> " +
            " lvl1 = #{lvl1} and " +
            "</if>" +
            "<if test='lvl2 != null and lvl2 != -1L'> " +
            " lvl2 = #{lvl2} and " +
            "</if>" +
            "1=1 " +
            "group by contentName,albumName order by count desc limit 20" +
            "</script>")
    List<TagCountResult> topPlayByLvlBetweenDate(@Param("start")Integer start,
                                                 @Param("end") Integer end,
                                                 @Param("lvl1") Long lvl1,
                                                 @Param("lvl2") Long lvl2);

    @Select("select count(1) AS count from dw_player where mdate >= #{start} and mdate <= #{end} and contentId = #{contentId}")
    Long countAudioPlayBetweenDate(@Param("start")Integer start,
                                   @Param("end") Integer end,
                                   @Param("contentId") Long contentId);

    @Select("select count(1) AS count from dw_player where mdate >= #{start} and mdate <= #{end} and authorId = #{authorId}")
    Long countAudioPlayBetweenDateForAuthor(@Param("start")Integer start,
                                            @Param("end") Integer end,
                                            @Param("authorId") Long authorId);

    @Select("select count(1) AS count from dw_player where authorId = #{authorId}")
    Long countAudioPlayForAuthor(@Param("authorId") Long authorId);


    @Select("select sum(lestenSec) AS sum from dw_player where mdate >= #{start} and mdate <= #{end} and contentId = #{contentId}")
    Long sumAudioPlaySecBetweenDate(@Param("start")Integer start,
                                     @Param("end") Integer end,
                                     @Param("contentId") Long contentId);

    @Select("select sum(lestenSec) AS sum from dw_player where mdate >= #{start} and mdate <= #{end} and albumId = #{albumId}")
    Long sumAlbumPlaySecBetweenDateById(@Param("start")Integer start,
                                        @Param("end") Integer end,
                                        @Param("albumId") Long albumId);

    @Select("select mHour from (select count(1) AS count,mHour from dw_player where mdate >= #{start} and mdate <= #{end} and contentId = #{contentId} group by mHour order by count desc limit 1) as a")
    Integer getAudioPlayTopHourBetweenDate(@Param("start")Integer start,
                                            @Param("end") Integer end,
                                            @Param("contentId") Long contentId);

    @Select("select sum(contentSec) AS sum from dw_player where mdate >= #{start} and mdate <= #{end} and authorName = #{authorName}")
    Long sumAuthorContentSecBetweenDate(@Param("start")Integer start,
                                       @Param("end") Integer end,
                                       @Param("authorName") String authorName);

    @Select("select sum(lestenSec) AS sum from dw_player where mdate >= #{start} and mdate <= #{end} and authorName = #{authorName}")
    Long sumAuthorPlaySecBetweenDate(@Param("start")Integer start,
                                     @Param("end") Integer end,
                                     @Param("authorName") String authorName);

    @Select("select mHour from (select count(1) AS count,mHour from dw_player where mdate >= #{start} and mdate <= #{end} and authorName = #{authorName} group by mHour order by count desc limit 1) as a")
    Integer getAuthorPlayTopHourBetweenDate(@Param("start")Integer start,
                                            @Param("end") Integer end,
                                            @Param("authorName") String authorName);
    @Select("select count(1) AS count from dw_player where mdate >= #{start} and mdate <= #{end} and authorName = #{authorName}")
    Long countAuthorPlayBetweenDate(@Param("start")Integer start,
                                    @Param("end") Integer end,
                                    @Param("authorName") String authorName);

    @Select("<script>"
            + "select sum(contentSec) AS sum from dw_player "
            + "<where> "
            + "mdate &gt;= #{start} and mdate &lt;= #{end} and "

            + "<if test='contentIds != null and contentIds.size()>0'> "
            + "contentId IN "
            + "<foreach item='contentId' collection='contentIds' open='(' close=')' separator=','> "
            + "#{contentId}"
            + "</foreach> AND "
            + "</if>"

            + "1=1 "
            + "</where>"
            + "</script>")
    Long sumAlbumContentSecBetweenDate(@Param("start")Integer start,
                                       @Param("end") Integer end,
                                       @Param("contentIds") List<Long> contentIds);


//    @Select("select count(1) AS count from dw_player where mdate >= #{start} and mdate <= #{end} and contentId in (${contentIds})")
    @Select("<script>"
            + "SELECT count(1) AS count "
            + "FROM dw_player "
            + "<where> "
            + "mdate &gt;= #{start} and mdate &lt;= #{end} and "

            + "<if test='contentIds != null and contentIds.size()>0'> "
            + "contentId IN "
            + "<foreach item='contentId' collection='contentIds' open='(' close=')' separator=','> "
            + "#{contentId}"
            + "</foreach> AND "
            + "</if>"

            + "1=1 "
            + "</where>"
            + "</script>")
    Long countAlbumPlayBetweenDate(@Param("start")Integer start,
                                   @Param("end") Integer end,
                                   @Param("contentIds") List<Long> contentIds);

//    @Select("select sum(lestenSec) AS sum from dw_player where mdate >= #{start} and mdate <= #{end} and contentId  in (${contentIds})")
    @Select("<script>"
            + "select sum(lestenSec) AS sum from dw_player "
            + "<where> "
            + "mdate &gt;= #{start} and mdate &lt;= #{end} and "

            + "<if test='contentIds != null and contentIds.size()>0'> "
            + "contentId IN "
            + "<foreach item='contentId' collection='contentIds' open='(' close=')' separator=','> "
            + "#{contentId}"
            + "</foreach> AND "
            + "</if>"

            + "1=1 "
            + "</where>"
            + "</script>")
    Long sumAlbumPlaySecBetweenDate(@Param("start")Integer start,
                                    @Param("end") Integer end,
                                    @Param("contentIds") List<Long> contentIds);

//    @Select("select mHour from (select count(1) AS count,mHour from dw_player where mdate >= #{start} and mdate <= #{end} and contentId in (${contentIds}) group by mHour order by count desc limit 1) as a")
    @Select("<script>"
            + "select mHour from (select count(1) AS count,mHour from dw_player "
            + "<where> "
            + "mdate &gt;= #{start} and mdate &lt;= #{end} and "

            + "<if test='contentIds != null and contentIds.size()>0'> "
            + "contentId IN "
            + "<foreach item='contentId' collection='contentIds' open='(' close=')' separator=','> "
            + "#{contentId}"
            + "</foreach> AND "
            + "</if>"

            + "1=1 "
            + "</where>"
            + "group by mHour order by count desc limit 1) as a"
            + "</script>")
    Integer getAlbumPlayTopHourBetweenDate(@Param("start")Integer start,
                                           @Param("end") Integer end,
                                           @Param("contentIds") List<Long> contentIds);

    @Select("select sum(contentSec) AS sum from dw_player where mdate >= #{start} and mdate <= #{end} and contentId = #{contentId}")
    Long sumAudioContentSecBetweenDate(@Param("start")Integer start,
                                       @Param("end") Integer end,
                                       @Param("contentId") Long contentId);

    @Select("select sum(contentSec) AS sum from dw_player where mdate >= #{start} and mdate <= #{end} and albumId = #{albumId}")
    Long sumAlbumContentSecBetweenDateById(@Param("start")Integer start,
                                           @Param("end") Integer end,
                                           @Param("albumId") Long albumId);


    @Select("select count(1) as count,mdate from dw_player where mdate >= #{start} and mdate <= #{end} and dictCode = #{dictCode} and contentId = #{contentId} group by mdate")
    List<CountResult> countAudioPlayTimeByDictCode(@Param("start")Integer start,
                                                   @Param("end")Integer end,
                                                   @Param("contentId") Long contentId,
                                                   @Param("dictCode")String dictCode);

    @Select("select count(1) as count,mdate from dw_player where mdate >= #{start} and mdate <= #{end} and dictCode = #{dictCode} and albumId = #{albumId} group by mdate")
    List<CountResult> countAblumPlayTimeByDictCode(@Param("start")Integer start,
                                                   @Param("end")Integer end,
                                                   @Param("albumId") Long albumId,
                                                   @Param("dictCode")String dictCode);

    @Select("select count(1) as count,mdate from (select mdate,imei from dw_player where mdate >= #{start} and mdate <= #{end} and dictCode = #{dictCode} and contentId = #{contentId} group by mdate,imei) as a group by mdate")
    List<CountResult> countImeiAudioPlayTimeByDictCode(@Param("start")Integer start,
                                                       @Param("end")Integer end,
                                                       @Param("contentId") Long contentId,
                                                       @Param("dictCode")String dictCode);

    @Select("select count(1) as count,mdate from (select mdate,imei from dw_player where mdate >= #{start} and mdate <= #{end} and dictCode = #{dictCode} and albumId = #{albumId} group by mdate,imei) as a group by mdate")
    List<CountResult> countImeiAlbumPlayTimeByDictCode(@Param("start")Integer start,
                                                       @Param("end")Integer end,
                                                       @Param("albumId") Long albumId,
                                                       @Param("dictCode")String dictCode);

    @Select("select count(1) as count,mdate from dw_player where mdate >= #{start} and mdate <= #{end} and contentId = #{contentId} group by mdate")
    List<CountResult> countAudioPlayTime(@Param("start")Integer start,
                                         @Param("end")Integer end,
                                         @Param("contentId") Long contentId);

    @Select("<script>" +
            "select count(1) as count,mdate from dw_player where mdate &gt;= #{start} and mdate &lt;= #{end} and " +
            "<if test='operateGroup != null and operateGroup != -1'>" +
            "operate_group = #{operateGroup} and " +
            "</if>" +
            "1=1 group by mdate" +
            "</script>")
    List<CountResult> countPlayTimeByOperateGroup(@Param("start")Integer start,
                                                  @Param("end")Integer end,
                                                  @Param("operateGroup") Integer operateGroup);

    @Select("select count(1) as count,mdate from dw_player where mdate >= #{start} and mdate <= #{end} and albumId = #{albumId} group by mdate")
    List<CountResult> countAblumPlayTime(@Param("start")Integer start,
                                         @Param("end")Integer end,
                                         @Param("albumId") Long albumId);

    @Select("select count(1) as count,mdate from (select mdate,imei from dw_player where mdate >= #{start} and mdate <= #{end} and contentId = #{contentId} group by mdate,imei) as a group by mdate")
    List<CountResult> countAudioPeoplePlayTime(@Param("start")Integer start,
                                               @Param("end")Integer end,
                                               @Param("contentId") Long contentId);

    @Select("select count(1) as count,mdate from (select mdate,imei from dw_player where mdate >= #{start} and mdate <= #{end} and albumId = #{albumId} group by mdate,imei) as a group by mdate")
    List<CountResult> countAlbumPeoplePlayTime(@Param("start")Integer start,
                                               @Param("end")Integer end,
                                               @Param("albumId") Long albumId);


    @Select("select count(1) as playCount, count(1)/count(distinct imei) as playCountAvg, sum(lestenSec) as playTime, sum(lestenSec)/count(distinct imei) as playTimeAvg from dw_player where mdate >= #{start} and mdate <= #{end}")
    ContentSimpleRsp getContentSimpleRspBetweenDate(@Param("start")Integer start,
                                                    @Param("end") Integer end);


    @Select("SELECT                                                                                                                          \n" +
            " count(1) AS allPlayTime,                                                                                                    \n" +
            " count(IF (dictCode = '600000000000',TRUE,NULL)) AS appPlayCount,                                                                                                         \n" +
            " count(IF (dictCode = '600000000002',TRUE,NULL)) AS h5PlayCount,                                                                                                             \n" +
            " count(DISTINCT imei) AS allPlayPeople,                                                                                      \n" +
            " count(DISTINCT imei, IF (dictCode = '600000000000',TRUE,NULL)) as appPlayPeople,                                                                                                                 \n" +
            " count(DISTINCT imei, IF (dictCode = '600000000002',TRUE,NULL)) as h5PlayPeople,                                                                                                                     \n" +
            " sum(lestenSec) / sum(contentSec) AS playAvg,                                                                                \n" +
            " mdate AS date                                                                                                                      \n" +
            " FROM dw_player WHERE                                                                                                                       \n" +
            " mdate >= #{start} and mdate <= #{end} and contentId = #{contentId}                                                                                                         \n" +
            " GROUP BY mdate                                                                                                                      ")
        List<ContentDataAudioTableResult> getContentDateAudioTableResult(@Param("start")Integer start,
                                                                        @Param("end")Integer end,
                                                                        @Param("contentId") Long contentId);


    @Select("<script>" +
            "SELECT                                                                                                                          \n" +
            " count(1) AS allPlayTime,                                                                                                    \n" +
            " count(IF (dictCode = '600000000000',TRUE,NULL)) AS appPlayCount,                                                                                                         \n" +
            " count(IF (dictCode = '600000000002',TRUE,NULL)) AS h5PlayCount,                                                                                                             \n" +
            " count(DISTINCT imei) AS allPlayPeople,                                                                                      \n" +
            " count(DISTINCT imei, IF (dictCode = '600000000000',TRUE,NULL)) as appPlayPeople,                                                                                                                 \n" +
            " count(DISTINCT imei, IF (dictCode = '600000000002',TRUE,NULL)) as h5PlayPeople,                                                                                                                     \n" +
            " sum(lestenSec) / sum(contentSec) AS playAvg,                                                                                \n" +
            " mdate AS date," +
            " contentId as audioId                                                                                                                   \n" +
            " FROM dw_player <where>                                                                                                                     \n" +
            " mdate &gt;= #{start} and mdate &lt;= #{end} and " +
            "<if test='contentIds != null and contentIds.size()>0'> "+
            "contentId IN " +
            "<foreach item='contentId' collection='contentIds' open='(' close=')' separator=','> "+
            "#{contentId}"+
            "</foreach> AND "+
            "</if>"+
            " 1 = 1  </where>                                                                                                       \n" +
            " GROUP BY mdate,contentId                                                                                                                      "+
            " </script>")
    List<ContentDataAudioTableResult> getContentDateAudioTableResults(@Param("start")Integer start,
                                                                     @Param("end")Integer end,
                                                                     @Param("contentIds") List<Long> contentIds);

    @Select("<script>" +
            "SELECT                                                                                                                          \n" +
            " count(1) AS allPlayTime,                                                                                                    \n" +
            " count(IF (dictCode = '600000000000',TRUE,NULL)) AS appPlayCount,                                                                                                         \n" +
            " count(IF (dictCode = '600000000002',TRUE,NULL)) AS h5PlayCount,                                                                                                             \n" +
            " count(DISTINCT imei) AS allPlayPeople,                                                                                      \n" +
            " count(DISTINCT imei, IF (dictCode = '600000000000',TRUE,NULL)) as appPlayPeople,                                                                                                                 \n" +
            " count(DISTINCT imei, IF (dictCode = '600000000002',TRUE,NULL)) as h5PlayPeople,                                                                                                                     \n" +
            " sum(lestenSec) / sum(contentSec) AS playAvg,                                                                                \n" +
            " mdate AS date," +
            " albumId                                                                                                                    \n" +
            " FROM dw_player <where>                                                                                                                     \n" +
            " mdate &gt;= #{start} and mdate &lt;= #{end} and " +
            "<if test='albumIds != null and albumIds.size()>0'> "+
            "albumId IN " +
            "<foreach item='albumId' collection='albumIds' open='(' close=')' separator=','> "+
            "#{albumId}"+
            "</foreach> AND "+
            "</if>"+
            " 1 = 1  </where>                                                                                                       \n" +
            " GROUP BY mdate,albumId                                                                                                                      "+
            " </script>")
    List<ContentDataAuthorTableResult> getContentDateAlbumTableResults(@Param("start")Integer start,
                                                                       @Param("end")Integer end,
                                                                       @Param("albumIds") List<Long> albumIds);
}
